package tt.dz.dao.ext.common;

import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import tt.dz.dao.base.OmsCommonDao;
import tt.dz.entity.base.OmsChargerShow;
import tt.dz.entity.base.OmsCommonParam;
import tt.dz.entity.base.OmsSplitratioScheme;
import tt.dz.entity.charger.OmsCharger;
import tt.dz.entity.exception.OmsDaoException;

import java.util.LinkedList;
import java.util.List;
import java.util.Map;
/**
 * 分成方案
 * @author Administrator
 *
 */
@Repository
public class OmsSplitratioSchemeDao extends OmsCommonDao<OmsSplitratioScheme>
{
	/**
	 * 查询当前最大的方案号
	 */
	public static final String MAX_SPLITRATIO_NO = "select Max(scheme_no) from oms_splitratio_scheme";
	/**
	 * 查询方案列表的sql
	 */
	public static final String FIND_SHEME_LST = "select scheme_no,scheme_name,service_no,case when entity_id='*' then '1' else '0' end entity_id,valid_flag from oms_splitratio_scheme group by scheme_no,scheme_name,case when entity_id='*' then '1' else '0' end,valid_flag order by scheme_no asc";
	/**
	 * 查询所有的方案的总数sql
	 */
	public static final String FIND_COUNT_SHEME_LST = "select count(1) from (select 1 from oms_splitratio_scheme WHERE valid_flag = 1 GROUP BY scheme_no,scheme_name) t";
	/**
	 * 删除指定方案号的方案
	 */
	public static final String DELETE_SHEME_NO = "delete from oms_splitratio_scheme where scheme_no = ?";
	
	/**
	 * 删除指定方案号对应桩信息
	 */
	public static final String DELETE_SPLITRATIO_SNO  = "delete from oms_service_splitratio  where scheme_no = ?";
	/**
	 * 逻辑删除方案号
	 */
	public static final String UPDATE_SHEME_NO  = "update oms_splitratio_scheme set valid_flag = 0 where scheme_no = ?";
	
	/**
	 * 逻辑删除对应桩信息
	 */
	public static final String UPDATE_SPLITRATIO_SNO  = "update oms_service_splitratio set valid_flag = 0 where scheme_no = ?";
	/**
	 * 查询所有分成的实体
	 */
	public static final StringBuffer FIND_ENTITY_ID_SHEME_NO = new StringBuffer();
	/**
	 * 根据方案号查询电桩列表信息
	 */
	public static final StringBuffer FIND_CHARGER_SHEME_NO = new StringBuffer();
//	/**
//	 * 根据方案号查询电桩列表总数
//	 */
//	public static final String FIND_CHARGER_COUNT_SHEME_NO = "select count(charger_id) from oms_service_splitratio where scheme_no = ?";
	/**
	 * 查询单个方案的sql
	 * 
	 * 采用group by查询相比distinct快。
	 * 不过数据量小，性能应该差不多。
	 */
	public static final String FIND_SHEME = "select scheme_no,scheme_name,valid_flag,service_no from oms_splitratio_scheme osss where scheme_no = ?  group by scheme_no,scheme_name ";
	
	static 
	{
		FIND_ENTITY_ID_SHEME_NO.append("SELECT oss.* FROM oms_splitratio_scheme oss WHERE oss.entity_id = '*' AND oss.scheme_no = ? union all ")
		.append(" select ").
		append("oss.* from oms_operation_sp_list oom left join oms_splitratio_scheme oss on oom.oper_merchant_id = oss.entity_id ").
		append("where oss.entity_type = 1  and oom.valid_flag = 1 and scheme_no = ? union all ").
		append("select ").
		append("oss.* from oms_operation_branch_sp_list oom left join oms_splitratio_scheme oss on oom.oper_merchant_branch_id = oss.entity_id ").
		append("where oss.entity_type = 2  and oom.valid_flag = 1 and scheme_no = ? union all ").
		append("select ").
		append("oss.* from oms_factory_list oom left join oms_splitratio_scheme oss on oom.factory_id = oss.entity_id ").
		append("where oss.entity_type = 3  and oom.valid_flag = 1 and scheme_no = ? union all ").
		append("select oss.* from oms_normal_sp_list oom left join oms_splitratio_scheme oss on oom.merchant_id = oss.entity_id ").
		append("where oss.entity_type = 4  and oom.valid_flag = 1 and scheme_no = ?");
		
		//查询分成的桩列表
		
		
		FIND_CHARGER_SHEME_NO.append("select oc.*,").
		append("os.station_name,os.address,").
		append("ofl.factory_name,").
		append("case when oc.entity_type = 1 then ").
		append("(select oper_name from oms_operation_sp_list  oom where oper_merchant_id = oc.entity_id) ").
		append("when oc.entity_type = 2 then ").
		append("(select oper_name from oms_operation_branch_sp_list  oom where oper_merchant_branch_id = oc.entity_id) ").
		append("when oc.entity_type = 3 then ").
		append("(select factory_name from oms_factory_list  oom where factory_id = oc.entity_id) ").
		append("when oc.entity_type = 4 then ").
		append("(select name from oms_normal_sp_list  oom where merchant_id = oc.entity_id) ").
		append("end as entity_name from ").
		append("(select * from oms_charger oc where charger_id in ").
		append("(select charger_id from oms_service_splitratio where scheme_no = ? and service_no = ?) and oc.valid_flag = 1) oc ").
		append("left join oms_station os on oc.station_id = os.station_id ").
		append("left join oms_factory_list ofl on oc.factory_id = ofl.factory_id ");
		//append("where oc.city_code = '' and (oc.charger_id like '%jj%' or os.station_name like '%%')")

	}
	
	/**
	 * 查询当前最大的序列号
	 * @return
	 */
	public int findMaxSplitratioNo()throws OmsDaoException
	{
		try
		{
			Integer maxNo = this.getJdbcTemplate().queryForInt(MAX_SPLITRATIO_NO);
			if (maxNo != null)
			{
				return maxNo;
			}
			else
			{
				return 0;
			}
		}
		catch(Exception e)
		{
			throw new OmsDaoException(e);
		}
	}
	/**
	 * 分页查询方案
	 * @param param
	 * @return
	 * @throws OmsDaoException
	 */
	public List<OmsSplitratioScheme> findSchemesByPage(OmsCommonParam param)throws OmsDaoException
	{
		StringBuilder sb = new StringBuilder();
		int limitOne = 0;
		int limitTwo = 0;
		sb.append(FIND_SHEME_LST);
		if (param != null &&
				param.getPageNo() != null &&
				param.getPageSize() != null &&
				param.getPageSize() != 0)
		{
			    limitOne = (param.getPageNo()-1)*param.getPageSize();
			    limitTwo = param.getPageSize();
				sb.append(" LIMIT ?,?");
		}
		
		try
		{
			if (limitTwo > 0)
			{
				return this.getJdbcTemplate().query(sb.toString(), new Object[]{limitOne,limitTwo},new BeanPropertyRowMapper<>(OmsSplitratioScheme.class));
			}
			else
			{
				return this.getJdbcTemplate().query(sb.toString(), new BeanPropertyRowMapper<>(OmsSplitratioScheme.class));
			}
		}
		catch(Exception e)
		{
			throw new OmsDaoException(e);
		}
	}
	/**
	 * 查询总的方案数量
	 * @return
	 * @throws OmsDaoException
	 */
	public Integer findCountSchemes()throws OmsDaoException
	{
		try
		{
			return (Integer) this.getJdbcTemplate().queryForObject(FIND_COUNT_SHEME_LST, Integer.class);
		}
		catch(Exception e)
		{
			throw new OmsDaoException(e);
		}
	}
	/**
	 * 方案号查询相关的的分成实体
	 * @param schemeNo
	 * @return
	 * @throws OmsDaoException
	 */
	public List<OmsSplitratioScheme> findSchemeDetailsEntitys(String schemeNo)throws OmsDaoException
	{
		try
		{
			return this.getJdbcTemplate().query(FIND_ENTITY_ID_SHEME_NO.toString(), new Object[]{schemeNo,schemeNo,schemeNo,
					schemeNo,schemeNo}, new BeanPropertyRowMapper<>(OmsSplitratioScheme.class));
		}
		catch(Exception e)
		{
			throw new OmsDaoException(e);
		}
	} 
	/**
	 * 根据方案号，查询电桩列表
	 * @param schemeNo
	 * @param cityCode
	 * @param likeValue
	 * @param param
	 * @return
	 * @throws OmsDaoException
	 */
	public List<OmsChargerShow> findChargersBySchemeNo(String schemeNo,String serviceNo,String cityCode,String likeValue,OmsCommonParam param)throws OmsDaoException
	{
		StringBuilder sb = new StringBuilder();
		List<Object> params = new LinkedList<>();
		params.add(schemeNo);
		params.add(serviceNo);
		StringBuilder likeParam = null;
		int limitOne = 0;
		int limitTwo = 0;
		sb.append(FIND_CHARGER_SHEME_NO);
		boolean whereExists = false;
		if (StringUtils.isNotEmpty(cityCode))
		{
			if (!whereExists)
			{
				sb.append(" WHERE ");
				whereExists = true;
			}
			params.add(cityCode);
			sb.append(" oc.city_code = ? ");
		}
		
		if (StringUtils.isNotEmpty(likeValue))
		{
			likeParam = new StringBuilder();
			likeParam.append("%").append(likeValue).append("%");
			if (!whereExists)
			{
				sb.append(" WHERE ");
				whereExists = true;
			}
			else
			{
				sb.append(" AND ");
			}
			params.add(likeParam.toString());
			params.add(likeParam.toString());
			sb.append(" (oc.charger_id like ? or os.station_name like ?) ");
		}
		if (param != null &&
				param.getPageNo() != null &&
				param.getPageSize() != null &&
				param.getPageSize() != 0)
		{
			    limitOne = (param.getPageNo()-1)*param.getPageSize();
			    limitTwo = param.getPageSize();
			    params.add(limitOne);
			    params.add(limitTwo);
				sb.append(" LIMIT ?,?");
		}
		
		try
		{
				return getJdbcTemplate().query(sb.toString(),params.toArray(), new BeanPropertyRowMapper<>(OmsChargerShow.class));
		}
		catch(Exception e)
		{
			throw new OmsDaoException(e);
		}
	}
	
	
	public void deleteShemeByShemeNo(String schemeNo)throws OmsDaoException
	{
		try
		{
			Object[] args ={schemeNo};
			getJdbcTemplate().update(DELETE_SHEME_NO, args);
		}
		catch(Exception e)
		{
			throw new OmsDaoException(e);
		}
	}
	
	public void deleteSplitratioByShemeNo(String schemeNo)throws OmsDaoException
	{
		try
		{
			Object[] args ={schemeNo};
			getJdbcTemplate().update(DELETE_SPLITRATIO_SNO, args);
		}
		catch(Exception e)
		{
			throw new OmsDaoException(e);
		}
	}
	
	public void logicDelShemeByShemeNo(String schemeNo)throws OmsDaoException
	{
		try
		{
			Object[] args ={schemeNo};
			getJdbcTemplate().update(UPDATE_SHEME_NO, args);
		}
		catch(Exception e)
		{
			throw new OmsDaoException(e);
		}
	}
	
	public void logicDelSplitratioBySchemeNo(String schemeNo)throws OmsDaoException
	{
		try
		{
			Object[] args ={schemeNo};
			getJdbcTemplate().update(UPDATE_SPLITRATIO_SNO, args);
		}
		catch(Exception e)
		{
			throw new OmsDaoException(e);
		}
	}

	
	public Map<String,Object> findSchemeBySchemeNo(String schemeNo)throws OmsDaoException
	{
		try
		{
			Object[] args ={schemeNo};
			return getJdbcTemplate().queryForMap(FIND_SHEME, args);
		}
		catch(EmptyResultDataAccessException e)
		{
			return null;
		}
		catch(Exception e)
		{
			throw new OmsDaoException(e);
		}
	}
	public List<OmsSplitratioScheme> findSchemeByChargerId(OmsCharger omsCharger) {
		if (StringUtils.isBlank(omsCharger.getOper_merchant_id())) {
			return null;
		}
		
		StringBuilder sb = new StringBuilder("select distinct a.scheme_no,a.scheme_name,a.service_no,a.valid_flag ")  
				                     .append("from oms_splitratio_scheme a where a.entity_id ='*' ")
				                     .append("union all ");
		if (StringUtils.isBlank(omsCharger.getEntity_id())) {
			                       sb.append("select distinct a.scheme_no,a.scheme_name,a.service_no,a.valid_flag ") 
			                         .append("from oms_splitratio_scheme a,oms_charger b ")
			                         .append("where b.charger_id='" + omsCharger.getCharger_id()+ "' and  ")
			                         .append("a.entity_id = b.oper_merchant_id ");
		}else{
			                       sb.append("select ta.scheme_no,ta.scheme_name,ta.service_no,ta.valid_flag from ") 
                                     .append("(select distinct a.scheme_no,a.scheme_name,a.service_no,a.valid_flag ") 
						             .append("from oms_splitratio_scheme a,oms_charger b ")
						             .append("where b.charger_id='" + omsCharger.getCharger_id()+ "' and  ")
						             .append("a.entity_id = b.oper_merchant_id )ta,")
                                     .append("(select distinct a.scheme_no,a.scheme_name,a.service_no,a.valid_flag ") 
						             .append("from oms_splitratio_scheme a,oms_charger b ")
						             .append("where b.charger_id='" + omsCharger.getCharger_id()+ "' and  ")
						             .append("a.entity_id = b.entity_id )tb ")
						             .append("where  ta.scheme_no=tb.scheme_no");
		}
	    
		return this.getJdbcTemplate().query(sb.toString(), new BeanPropertyRowMapper<>(OmsSplitratioScheme.class));
			
		
	}
	
}
